﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Runtime.InteropServices;
using System.Data.OleDb;
using AxSawyer.Objects;

namespace AxSawyer
{
    public interface IAxSawyerExt
    {
        void PrepareNames();
        string Msg();
        string DSizeXstr(Int32 detId);
        string DSizeYstr(Int32 detId);
        string MaterialNameById(String matId);
        void OpenDataBase();
        void CloseDataBase();
    }

    [ClassInterface(ClassInterfaceType.AutoDual)]
    public class AxSawyer : IAxSawyerExt
    {
        private static OleDbConnection connection = new OleDbConnection(new Settings().ConnectionString);

        private String _status = String.Empty;
        private List<Design> _designs = new List<Design>();
        private List<Detail> _details = new List<Detail>();

        /// <summary> Open data base </summary>
        public void OpenDataBase()
        {
            try
            {
                switch (connection.State)
                {
                    case ConnectionState.Closed:
                        connection.Open();
                        break;
                    case ConnectionState.Open:
                        break;
                    case ConnectionState.Connecting:
                        break;
                    case ConnectionState.Executing:
                        break;
                    case ConnectionState.Fetching:
                        break;
                    case ConnectionState.Broken:
                        break;
                    default:
                        throw new ArgumentOutOfRangeException();
                }
            }
            catch (Exception exception)
            {
                _status += "Error: " + exception.ToString() + Environment.NewLine;
            }
        }

        /// <summary> Close data base </summary>
        public void CloseDataBase()
        {
            try
            {
                switch (connection.State)
                {
                    case ConnectionState.Closed:
                        break;
                    case ConnectionState.Open:
                        connection.Close();
                        break;
                    case ConnectionState.Connecting:
                        break;
                    case ConnectionState.Executing:
                        break;
                    case ConnectionState.Fetching:
                        break;
                    case ConnectionState.Broken:
                        break;
                    default:
                        throw new ArgumentOutOfRangeException();
                }
            }
            catch (Exception exception)
            {
                _status += "Error: " + exception.ToString() + Environment.NewLine;
            }
        }

        /// <summary> Prepare list of today added designs to woody db </summary>
        private void GetTodayDesigns()
        {
            try
            {
                String q = @"
                            SELECT 
                                Design.IDGoods,
                                Design.Name                                            
                            FROM 
                                Design
                            WHERE 
                                Design.DataEdit = @dataEdit
                            ";

                OleDbCommand cmd = new OleDbCommand(q, connection);
                cmd.Parameters.AddWithValue("@dataEdit", DateTime.Now.Date.ToShortDateString());

                OleDbDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Design design = new Design();
                        design.IdGoods = Convert.ToInt32(reader["IDGoods"]);
                        design.Name = reader["Name"].ToString();

                        _designs.Add(design);
                    }
                    reader.Close();
                }
            }
            catch (Exception exception)
            {
                _status += exception.ToString() + Environment.NewLine;
            }
        }

        /// <summary> Prepare list with renewable details for today added designs </summary>
        private void GetDesignDetails(Design design)
        {
            try
            {
                String q = @"
                            SELECT                                         
                                Details.IDdet, 
                                Details.Name 
                            FROM 
                                Details 
                            WHERE
                                Details.IDGoods = @design_idGoods
                            ";

                OleDbCommand cmd = new OleDbCommand(q, connection); // creating query command
                cmd.Parameters.AddWithValue("@design_idGoods", design.IdGoods);

                OleDbDataReader reader = cmd.ExecuteReader(); // executes query
                if (reader != null)
                {
                    while (reader.Read()) // if can read row from database
                    {
                        Detail detail = new Detail();
                        detail.IdDet = Convert.ToInt32(reader["IDdet"]);
                        detail.Name = reader["Name"].ToString();

                        _details.Add(detail);
                    }
                    reader.Close(); // after reading close reader
                }
            }
            catch (Exception exception)
            {
                _status += exception.ToString() + Environment.NewLine;
            }
        }

        /// <summary> Update detail name </summary>
        private void UpdateDetail(Detail detail)
        {
            try
            {
                String q = @"
                        UPDATE Details
                        SET
                            Details.Name = @newName
                        WHERE
                            Details.IDdet = @idDet
                        ";

                OleDbCommand cmd = new OleDbCommand(q, connection);
                cmd.Parameters.AddWithValue("@newName", String.Format("{0}{1}{2}", detail.Name, "&", detail.IdDet));
                cmd.Parameters.AddWithValue("@idDet", detail.IdDet);

                cmd.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                _status += exception.ToString() + Environment.NewLine;
            }
        }

        /// <summary> Detail dimestional X size </summary>
        public String DSizeXstr(Int32 detId)
        {
            String xSize = String.Empty;
            try
            {
                #region dX query
                String query =
                @"
                    SELECT
                        Details.Dx
                    FROM
                        Details
                    WHERE
                        Details.IDdet = @detailId
                 ";
                #endregion

                OleDbCommand cmd = new OleDbCommand(query, connection);
                cmd.Parameters.AddWithValue("@detailId", detId);

                OleDbDataReader reader = cmd.ExecuteReader();
                if(reader != null)
                {
                    while (reader.Read())
                    {
                        switch (reader[0].GetType().ToString())
                        {
                            case "System.Double":
                                xSize = String.Format("{0:0.##}", Convert.ToDouble(reader[0]) * 1000);
                                break;
                        }
                    }
                    reader.Close();
                }
            }
            catch (Exception exception)
            {
                _status += "Error: " + exception.ToString() + Environment.NewLine;
            }
            return xSize;
        }

        /// <summary> Detail dimensional Y size </summary>
        public String DSizeYstr(Int32 detId)
        {
            String ySize = String.Empty;
            try
            {
                #region dX query
                String query =
                @"
                    SELECT
                        Details.Dy
                    FROM
                        Details
                    WHERE
                        Details.IDdet = @detailId
                 ";
                #endregion

                OleDbCommand cmd = new OleDbCommand(query, connection);
                cmd.Parameters.AddWithValue("@detailId", detId);

                OleDbDataReader reader = cmd.ExecuteReader();
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        switch (reader[0].GetType().ToString())
                        {
                            case "System.Double":
                                ySize = String.Format("{0:0.##}", Convert.ToDouble(reader[0]) * 1000);
                                break;
                        }
                    }
                    reader.Close();
                }
            }
            catch (Exception exception)
            {
                _status += "Error: " + exception.ToString() + Environment.NewLine;
            }
            return ySize;
        }

        public String MaterialNameById(String matId)
        {
            String name = String.Empty;
            try
            {
                #region

                String query =
                    @"
                        SELECT 
                            Materials.Name 
                        FROM 
                            Materials 
                        WHERE 
                            Materials.IDmaterial = @matId;
                     ";

                #endregion

                OleDbCommand cmd = new OleDbCommand(query, connection);
                cmd.Parameters.AddWithValue("@matId", matId);

                OleDbDataReader reader = cmd.ExecuteReader();
                if(reader != null)
                {
                    while (reader.Read())
                    {
                        name = reader[0].ToString();
                    }
                    reader.Close();
                }
            }
            catch (Exception exception)
            {
                _status += "Error: " + exception.ToString() + Environment.NewLine;
            }
            return name;
        }

        public void PrepareNames()
        {
            try
            {
                // open database
                connection.Open();
                switch (connection.State)
                {
                    case ConnectionState.Closed:
                        break;
                    case ConnectionState.Open:
                        _status += " Database open. " + Environment.NewLine;
                        break;
                    case ConnectionState.Connecting:
                        break;
                    case ConnectionState.Executing:
                        break;
                    case ConnectionState.Fetching:
                        break;
                    case ConnectionState.Broken:
                        break;
                    default:
                        throw new ArgumentOutOfRangeException();
                }

                // prepare today added designs list 
                GetTodayDesigns(); 

                // get for each design details
                foreach (Design design in _designs)
                {
                    GetDesignDetails(design);
                }

                // upadate detail name
                int uc = 0;
                foreach (Detail detail in _details)
                {
                    if (!detail.Name.Contains("&")) // check if detail name renamed
                    {
                        UpdateDetail(detail);
                        uc++;
                    }
                }
                if(uc > 0)
                {
                    _status += String.Format(" Update successful. Updated: {0} details ", uc) + Environment.NewLine;
                }
                else
                {
                    _status += " Nothing to update. " + Environment.NewLine;
                }
            }
            catch (Exception exception)
            {
                _status += " Error: " + exception.ToString() + Environment.NewLine;
            }
            finally
            {
                connection.Close(); // close database
                _status += " Database close. " + Environment.NewLine ;
            }
        }

        public String Msg()
        {
            return _status;
        }
    }
}
